package io.gary.common.util.execel;

import com.sun.javafx.collections.UnmodifiableListSet;
import io.gary.common.util.execel.annotation.SheetColumn;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;


/**
 * 工具包-execel-execel导入导出工具类:
 * <p>
 *
 * @author gary.pu  2019-07-14
 */
public final class ExecelUtil {

    private static final Set<Class<?>> BASE_TYPES;
    public static final ExecelUtil instance = new ExecelUtil();

    private ExecelUtil() {}

    static {
        Class<?>[] types = {Integer.class, Long.class, String.class, Float.class, Double.class, Date.class};
        BASE_TYPES = new UnmodifiableListSet<>(Arrays.asList(types));
    }

    private String trimTitle(String title) {
        return title.replace("\n", "").replace("\t", "").replace(" ", "");
    }

    /**
     * 导入execel数据
     *
     * @return 返回值
     * @author gary.pu 2019-7-14
     */
    public <T> List<T> importData(InputStream is, Class<T> tClass)
        throws IOException, IllegalAccessException, InstantiationException {
        Workbook workbook = WorkbookFactory.create(is);
        List<ColumnMeta> columnMetaList = readMeta(tClass);
        Sheet sheet = workbook.getSheetAt(0);
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        int titleRowIndex = 0;
        if (CollectionUtils.isNotEmpty(mergedRegions)) {
            //只支持第一行为合并的
            titleRowIndex = 1;
        }
        for (CellRangeAddress cellAddresses : mergedRegions) {
            int firstRow = cellAddresses.getFirstRow();
            int lastRow = cellAddresses.getLastRow();
            int firstColumn = cellAddresses.getFirstColumn();
            int lastColumn = cellAddresses.getLastColumn();
            //只能合并一行作为 父标题
            if (firstRow != lastRow && firstRow != 1) {
                continue;
            }
            Cell cell = sheet.getRow(firstRow).getCell(firstColumn);
            String title = trimTitle(StringUtils.trim(cell.getStringCellValue()));
            //找到父标题
            ColumnMeta columnMeta = findMeta(title, columnMetaList);
            if (Objects.nonNull(columnMeta)) {
                //设置范围
                columnMeta.index = firstColumn;
                columnMeta.firstColumn = firstColumn;
                columnMeta.lastColumn = lastColumn;
                if (columnMeta.type.equals(List.class) || columnMeta.type.equals(Map.class)) {
                    columnMeta.childs = new ArrayList<>(lastColumn - firstColumn);
                }
            }
        }
        Row titleRow = sheet.getRow(titleRowIndex);
        for (short i = titleRow.getFirstCellNum(); i < titleRow.getLastCellNum(); i++) {
            Cell titleCell = titleRow.getCell(i);
            String title = trimTitle(titleCell.getStringCellValue());
            updateTitleMeta(title, i, columnMetaList);
        }
        List<T> datas = new LinkedList<>();
        for (int i = titleRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
            Row dataRow = sheet.getRow(i);
            T data = tClass.newInstance();
            for (short j = dataRow.getFirstCellNum(); j < dataRow.getLastCellNum(); j++) {
                ColumnMeta columnMeta = findMeta(j, columnMetaList);
                if (Objects.isNull(columnMeta)) {
                    continue;
                }
                Cell dataCell = dataRow.getCell(j);
                columnMeta.setData(data, dataCell);

            }
            datas.add(data);
        }
        return datas;
    }

    private static List<ColumnMeta> readMeta(Class<?> tClass) {
        //        if (! tClass.isAnnotationPresent(SheetDocument.class)) {
        //            throw new IllegalArgumentException("class must be mark SheetDocument");
        //        }
        Field[] fields = FieldUtils.getAllFields(tClass);
        //生成title信息
        List<ColumnMeta> columnMetaList = new LinkedList<>();
        for (Field field : fields) {
            if (! field.isAnnotationPresent(SheetColumn.class)) {
                continue;
            }

            SheetColumn sheetColumn = field.getAnnotation(SheetColumn.class);
            ColumnMeta columnMeta = new ColumnMeta();
            columnMeta.title = sheetColumn.value();
            columnMeta.nullable = sheetColumn.nullable();
            columnMeta.type = field.getType();
            columnMeta.field = field;
            if (! field.getType().isPrimitive() && ! BASE_TYPES.contains(field.getType())) {
                if (field.getType().equals(List.class)) {
                    Type genericType = field.getGenericType();
                    if (genericType instanceof ParameterizedType) {
                        ParameterizedType parameterizedType = (ParameterizedType)genericType;
                        Class<?> genericClazz = (Class<?>)parameterizedType.getActualTypeArguments()[0];
                        //只有实现了ExecelCell接口的列表才可以
                        if (! SheetCell.class.isAssignableFrom(genericClazz)) {
                            throw new IllegalArgumentException(
                                "invalid type list:" + genericClazz.getName() + ".must be SheetCell interface");
                        }
                        //设置值的类型
                        columnMeta.listChildType = (Class<? extends SheetCell>)genericClazz;
                        for (Type inters : genericClazz.getGenericInterfaces()) {
                            if (inters instanceof ParameterizedType) {
                                ParameterizedType intersParameterizedType = (ParameterizedType)inters;
                                if (! intersParameterizedType.getRawType().equals(SheetCell.class)) {
                                    continue;
                                }
                                Type childValueType = intersParameterizedType.getActualTypeArguments()[0];
                                columnMeta.listChildValueType = (Class<?>)childValueType;
                            }

                        }
                    }
                } else if (field.getType().equals(Map.class)) {

                } else {
                    columnMeta.childs = readMeta(field.getType());
                    if (CollectionUtils.isEmpty(columnMeta.childs)) {
                        continue;
                    }
                }

            }
            //            if (! BASE_TYPES.contains(field.getType()) && CollectionUtils.isEmpty(columnMeta.childs)) {
            //                continue;
            //            }
            columnMetaList.add(columnMeta);
        }
        return columnMetaList;
    }


    private static ColumnMeta findMeta(String name, List<ColumnMeta> columnMetaList) {
        for (ColumnMeta columnMeta : columnMetaList) {
            if (columnMeta.title.equals(name)) {
                return columnMeta;
            }
        }
        return null;
    }

    private static ColumnMeta findMeta(int index, List<ColumnMeta> columnMetaList) {
        for (ColumnMeta columnMeta : columnMetaList) {
            if (columnMeta.index == index) {
                return columnMeta;
            }
            if (columnMeta.firstColumn <= index && columnMeta.lastColumn >= index) {
                return columnMeta;
            }
        }
        return null;
    }

    private static void updateTitleMeta(String name, int index, List<ColumnMeta> columnMetaList) {
        for (ColumnMeta columnMeta : columnMetaList) {
            if (columnMeta.firstColumn <= index && columnMeta.lastColumn >= index) {
                int pos = index - columnMeta.firstColumn;
                if (columnMeta.type.equals(List.class)) {
                    ColumnMeta columnMeta1 = new ColumnMeta();
                    columnMeta1.type = columnMeta.listChildValueType;
                    columnMeta1.index = index;
                    columnMeta1.title = name;
                    columnMeta.childs.add(pos, columnMeta1);
                } else if (columnMeta.type.equals(Map.class)) {
                    ColumnMeta columnMeta1 = new ColumnMeta();
                    columnMeta1.type = Object.class;
                    columnMeta1.index = index;
                    columnMeta1.title = name;
                    columnMeta.childs.add(pos, columnMeta1);
                } else {
                    ColumnMeta columnMeta1 = findMeta(name, columnMeta.childs);
                    if (Objects.nonNull(columnMeta1)) {
                        columnMeta1.index = index;
                    }
                }
            } else if (columnMeta.title.equals(name)) {
                columnMeta.index = index;
            }
        }
    }

    private static class ColumnMeta {

        Class<? extends SheetCell> listChildType;
        Class<?> listChildValueType;
        Field field;

        /**
         * 标题
         */
        String title;
        /**
         * 索引
         */
        int index = - 1;
        /**
         * 第一列的索引
         */
        int firstColumn = - 1;
        /**
         * 最后一列的索引
         */
        int lastColumn = - 1;
        /**
         * 是否允许空
         */
        boolean nullable;
        /**
         * 类型
         */
        Class<?> type;

        /**
         * 孩子节点
         */
        List<ColumnMeta> childs;

        //subField: List => list of SheetCell,Map,Object

        void setData(Object data, Cell dataCell) throws IllegalAccessException, InstantiationException {
            if (Objects.isNull(dataCell)) {
                return;
            }
            int index = dataCell.getAddress().getColumn();
            if (BASE_TYPES.contains(this.type)) {
                Object value = getCellValue(dataCell, this.type, this.nullable);
                FieldUtils.writeField(this.field, data, value, true);
            } else if (this.type.equals(List.class)) {
                int pos = index - this.firstColumn;

                //初始化列表
                List<? super SheetCell> subValueList = (List<? super SheetCell>)FieldUtils
                    .readField(this.field, data, true);
                if (CollectionUtils.isEmpty(subValueList)) {
                    subValueList = new ArrayList<>(this.lastColumn - this.firstColumn);
                }
                ColumnMeta columnMeta = this.childs.get(pos);
                Object subValue = getCellValue(dataCell, columnMeta.type, columnMeta.nullable);
                //                Object subData = this.listChildValueType.newInstance();
                SheetCell subData = this.listChildType.newInstance();
                subData.setTitle(columnMeta.title);
                subData.setValue(subValue);
                subValueList.add(pos, subData);
                FieldUtils.writeField(this.field, data, subValueList, true);
            } else if (this.type.equals(Map.class)) {
                int pos = index - this.firstColumn;
                //初始化列表
                Map<String, Object> subValueMap = (Map<String, Object>)FieldUtils.readField(this.field, data, true);
                if (Objects.isNull(subValueMap)) {
                    subValueMap = new HashMap<>(this.lastColumn - this.firstColumn);
                }
                ColumnMeta columnMeta = this.childs.get(pos);
                Object subValue = getCellValue(dataCell, columnMeta.type, columnMeta.nullable);
                subValueMap.put(columnMeta.title, subValue);
                FieldUtils.writeField(this.field, data, subValueMap, true);
            } else if (CollectionUtils.isNotEmpty(this.childs)) {
                ColumnMeta columnMeta = findMeta(index, this.childs);
                if (Objects.isNull(columnMeta)) {
                    return;
                }
                Object subValue = getCellValue(dataCell, columnMeta.type, columnMeta.nullable);
                Object subData = FieldUtils.readField(this.field, data, true);
                if (Objects.isNull(subData)) {
                    subData = this.type.newInstance();
                }
                FieldUtils.writeField(columnMeta.field, subData, subValue, true);
                FieldUtils.writeField(this.field, data, subData, true);
                //                FieldUtils.writeField();
            }
        }
    }

    public static Object getCellValue(Cell cell, Class clazz, boolean allowNull) {
        try {
            if (Objects.isNull(cell)) {
                return null;
            }
            Objects.requireNonNull(clazz);
            Object value = null;
            if (clazz == String.class) {
                value = cell.getStringCellValue();
                value = value == null ? "" : value;
            } else if (clazz == Integer.class) {
                value = (int)cell.getNumericCellValue();
            } else if (clazz == Long.class) {
                value = (long)cell.getNumericCellValue();
            } else if (clazz == Float.class) {
                value = (float)cell.getNumericCellValue();
            } else if (clazz == Double.class) {
                value = cell.getNumericCellValue();
                value = value == null ? 0 : value;
            } else if (clazz == Date.class) {
                value = cell.getDateCellValue();
            }
            if (! allowNull) {
                Objects.requireNonNull(value);
            }
            return value;
        } catch (Exception e) {
            throw e;
        }
    }


}
